﻿
CREATE PROCEDURE [dbo].[proc_Department_Delete_News]
	(
		@Id INT,
		@temp INT
	)
AS
IF(@temp=1)--查看要删除的ID
    BEGIN
    	DECLARE @bmbh VARCHAR(50)
		set @bmbh=(SELECT bmbh FROM Department WHERE Id=@Id)	
		SELECT id,DName FROM Department WHERE SUBSTRING(bmbh,1,LEN(@bmbh))=@bmbh
    END
IF(@temp=0)	
	--select count(Id) From employee where departmentid=@Id and state <> 5
	select sum(a) from (
	select count(Id) as a From employee where departmentid=@Id and state <> 5
	union select count(Id) as a From Assets where departmentid=@Id and stateid =1 
	) as b

IF(@temp=2)
	Delete From Department Where Id=@Id
	
--	END
--		ELSE
--			RETURN 1
--		END
	
	RETURN
